package com.surekam.mnai.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.surekam.mnai.model.Address;
import com.surekam.mnai.model.MobileNumberAttribution;

/**
 * 手机号码归属信息dao层接口实现
 * 
 * @author lium
 * 
 */
@Component
public class MobileNumberAttributionDao extends BaseDao {

	private static Logger logger = LoggerFactory
			.getLogger(MobileNumberAttributionDao.class);

	private  NumberAttributionMapper numberAttributionMapper = new NumberAttributionMapper();

	/**
	 * MobileNumberAttribution 类与表 T_MOBILE_NUMBER_ARRTRIBUTION 的映射关系
	 *
	 */
	private static class NumberAttributionMapper implements
			RowMapper<MobileNumberAttribution> {

		@Override
		public MobileNumberAttribution mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			MobileNumberAttribution num = new MobileNumberAttribution();
			num.setMobileNumberSegment(rs.getString("MOBILE_NUMBER_SEGMENT"));
			num.setPhoneCardType(rs.getString("PHONE_CARD_TYPE"));
			Address address = new Address();
			address.setAddressId(rs.getInt("ADDRESS_ID"));
			num.setAddress(address);
			num.setLastUpdatedTime(rs.getDate("LAST_UPDATED_TIME"));
			return num;
		}
	}

	/**
	 * @return 所有的手机号码归属地信息
	 */
	public List<MobileNumberAttribution> queryAll() {
		String querySql = "SELECT * FROM t_mobile_number_attribution";
		return this.jdbcTemplate.query(querySql, numberAttributionMapper);
	}
	
	/**
	 * 分页查询
	 * 当id-(id+size)中的数据有id不连续时，size+1.到最后返回的集合数据大小为size,假如有数据
	 * 
	 * @param startID
	 *            起始id
	 * @param size
	 *            返回的集合数据容量
	 * @return
	 */
	public List<MobileNumberAttribution> listBySection(String startID, int size){
		String querySql = "SELECT * FROM _mobile_number_attribution WHERE MOBILE_NUMBER_SEGMENT BETWEEN ? AND ? ";
		return this.jdbcTemplate.query(querySql, numberAttributionMapper, startID,startID+size);
	}

	/**
	 * 按手机号码或手机号码段查询归属地信息
	 * 
	 * @param mobileNumberSegment
	 *            所要查的手机号码段
	 * @return 指定的手机号码段归属地信息
	 */
	public MobileNumberAttribution queryByMobileNumber(
			String mobileNumberSegment) {
		String querySql = "SELECT * FROM t_mobile_number_attribution WHERE MOBILE_NUMBER_SEGMENT LIKE '%?%'";
		return this.jdbcTemplate.queryForObject(querySql, numberAttributionMapper, mobileNumberSegment);
	}

	/**
	 * 按所在城市查询手机号码归属地信息列表
	 * 
	 * @param city
	 *            指定的城市
	 * @return 指定的城市的手机号码归属地信息
	 */
	public List<MobileNumberAttribution> queryByCity(String city) {
		String querySql = "SELECT * FROM t_mobile_number_attribution AS t1,t_address AS t2 " +
				"WHERE t2.CITY=? AND t1.ADDRESS_ID = t2.ADDRESS_ID";
		return this.jdbcTemplate.query(querySql, numberAttributionMapper,city);
	}

	/**
	 * 添加一条手机号码归属信息
	 * 
	 * @param numberAttribution
	 */
	public void insert(MobileNumberAttribution numberAttribution) {
		if(numberAttribution == null){
			throw new IllegalArgumentException("MobileNumberAttribution实例不能为null");
		}
		
		logger.info("插入新记录到MobileNumberAttribution表中");
		String insertSql = "INSERT INTO t_mobile_number_attribution(MOBILE_NUMBER_SEGMENT," +
				"ADDRESS_ID," + "PHONE_CARD_TYPE," + "last_updated_time" + ") VALUES(?,?,?,?)";
		this.jdbcTemplate.update(insertSql, numberAttribution.getMobileNumberSegment(),
				numberAttribution.getAddress().getAddressId(),numberAttribution.getPhoneCardType(),
				numberAttribution.getLastUpdatedTime());
		
		logger.info("插入新记录到MobileNumberAttribution表中");

	}

	/**
	 * 删除一条手机号码归属信息
	 * 
	 * @param mobileNumberSegment
	 */
	public void deleteByNumberSegment(String mobileNumberSegment) {
		logger.info("删除MobileNumberAttribution表中的记录: mobileNumberSegment=" + mobileNumberSegment);
		
		String deleteSql = "DELETE FROM t_mobile_number_attribution WHERE MOBILE_NUMBER_SEGMENT = ?";
		this.jdbcTemplate.update(deleteSql, mobileNumberSegment);
		
		logger.info("删除MobileNumberAttribution表中的记录: mobileNumberSegment=" + mobileNumberSegment + "成功!");

	}

	/**
	 * 修改手机号码归属信息
	 * 
	 * @param numberAttribution 要更新的信息
	 */
	public void update(MobileNumberAttribution numberAttribution) {
		if(numberAttribution == null){
			throw new IllegalArgumentException("MobileNumberAttribution实例不能为空");
		}
		
		logger.info("更新MobileNumberAttribution表中的记录: mobileNumberSegment=" + numberAttribution.getMobileNumberSegment());
		
		String updateSql = "UPDATE t_mobile_number_attribution SET ADDRESS_ID = ?," +
				"PHONE_CARD_TYPE = ?, " + "last_updated_time = ? " + "WHERE MOBILE_NUMBER_SEGMENT = ?";
		this.jdbcTemplate.update(updateSql, numberAttribution.getAddress(),numberAttribution.getPhoneCardType(),
				numberAttribution.getLastUpdatedTime(),numberAttribution.getMobileNumberSegment());
		
		logger.info("更新MobileNumberAttribution表中的记录: mobileNumberSegment=" + numberAttribution.getMobileNumberSegment()+"成功!");
		
	}

	/**
	 * 修改指定手机号码段的所在省份与城市。
	 * @param mobileNumberSegment 指定的手机号码段
	 * @param province 修改后的省份
	 * @param city  修改后的城市
	 */
	public void updateByNumberSegment(String mobileNumberSegment,String province,String city) {
		logger.info("更新MobileNumberAttribution表中的记录: mobileNumberSegment=" + mobileNumberSegment);
		
		String updateSql = "UPDATE t_mobile_number_attribution AS t1,t_address AS t2 SET " +
				"t2.PROVINCE = ?,t2.CITY = ? WHERE  MOBILE_NUMBER_SEGMENT = ? AND t1.ADDRESS_ID=t2.ADDRESS_ID";
		this.jdbcTemplate.update(updateSql, province,city,mobileNumberSegment);
		
		logger.info("更新MobileNumberAttribution表中的记录: mobileNumberSegment=" + mobileNumberSegment + "成功");
	}

}
